Combined query in SQL 1

[nitha]

Hi,
I have Three tables A,B ,C
i've to get recors frm B & C based on a value from A..my algorithm is somewhat like this

if A.Type = 'x' then
select B.Name,B.Age FROM B Where B.Code = A.Code
else if A.Type = 'y' then
select C.Name,C.Address From C where C.Code = A.Code

how could i write a single query for this..the main problem is in getting multiple fields from B and C...Can anyone help me?
Thanks
nitha

 

[nath]

Hi Nitha,

You could try something like:

SELECT B.Name,B.Age 
FROM B 
WHERE B.Code in (SELECT A.Code
                 FROM A
                 WHERE A.Type = 'x')
UNION
SELECT C.Name,C.Address 
FROM C 
WHERE C.Code in (SELECT A.Code
                 FROM A
                 WHERE A.Type = 'y')
[\CODE]

Nath

 

[LazyMe]

Not a UNION, a JOIN would do that.

The rest depends on the database that you're using (SQL server you can use a CASE WHEN a.type='x' THEN .... ELSE ...


Access does not support that, but has the IIF function build in, which does essentially the same.


Greetings,
Rick

 

[johnwm]

lazyme

<Not a UNION, a JOIN would do that>

Are you sure??? He wants both lots of records
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[LazyMe]

Yes, but since it's depending on a field in table a I think a JOIN would be more appropriate (either JOIN with B or JOIN with C). But the UNION as presented should also work, although it will need to be adjusted (but the same restriction will go for the JOIN variant):

I noticed now that both fields in table B and C are not of the same type (age is probably int or something and address is probably char or string or something). This will present a problem (which might be solved by adding dummy fields in both B and C queries).
Greetings,
Rick

 

[nitha]

thanks nath, that query is working fine..
as i've same type of fields in both tables its ok, otherwise i wont work

 

[SemperFiDownUnda]

you could do a OUTER join to tables B & C

SELECT A.Type, 
       B.Name,
       B.Age, 
       C.Name,
       C.Address  
  FROM A 
  LEFT OUTER JOIN B 
    ON B.Code = A.Code
  LEFT OUTER JOIN C
    PM C.Code = A.Code
[code]

 

[SharonLove]

Is LEFT JOIN and LEFT OUTER JOIN the same?

 

[LazyMe]

Yes it is. LEFT/RIGHT joins are both OUTER joins and I think most database systems will accept both notations.
Greetings,
Rick

 

[SharonLove]

Thankyou.

 

[VBOldTimer]

&quot;This will present a problem (which might be solved by adding dummy fields in both B and C queries&quot;

Just wanted to add that using alias fields will not solve the problem.
The problem is that with a UNION query, the field types must be the same.

Looks like using a JOIN is what is needed, which should work as long as there is a foreign key.

 

[LazyMe]

It can be solved by adding dummy fields.

By dummy fields I don't mean aliasing:
It would mean introducing one dummy field in the first query (to be left empty) the same type as the one missing in the second and the other way around. This way you've got all fields of the same type in both queries, just in both you've got one that's never filled....


Greetings,
Rick

 

[VBOldTimer]

Ok, I misunderstood what you meant by dummy fields.

This does work, but I have doubt if this (using a union query only) is the right way of going about this.

Or more so, if the tables, and the logic behind their relationship are even designed right.

I would think that there would be something like all basic primary data in table A, such as customer id, name, address, and in table B something like ordering information, and in table C order details.

But why would I have static information like name and age in one table, and static information like name and address in a second table for the same customer.

I could understand two different order detail tables, however with identical fields. Then two queries would be useful-
One which does a Union query (Query2) on the two order tables, and one (Query1) that gets the information from table A and does also join on Query2, the union query.

Oh well, maybe there is a specific situation where the above is called for.