|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
VeriSign Code Signing Digital Certificates provides assurance to end users. Read about this and more in the free white paper: “How to Digitally Sign Downloadable Code for Secure Content Transfer.” Learn More! |
|
#1
February 17th, 2004, 05:55 PM
|
||||
|
||||
Help with query. Joins? Unions?
Hi,
I have a few tables that I want to query and I need the output back in a certain way. This is an example of my problem. Users Table: ID, UserName 1 , Fred 2 , Bill DVD Table: ID, DVDName 1 , XMEN 2 , Terminiator 2 3 , MontyPhthon Holy Grail VHS Table: ID, VHSName 1 , Terminator 2 , StarWars DVD Users table: ID, UserID, DVD_ID 1 , 1 , 1 2 , 1 , 3 3 , 2 , 1 4 , 2 , 2 VHS Users Table: ID, UserID, VHS_ID 1 , 2 , 1 3 , 1 , 2 So, each person might 0 or more DVDs and 0 or more VHS. What I need to output is a flat table something like this. User, DVD , VHS Fred, XMEN , <null> Fred, MontyPhthon Holy Grail , <null> Fred, <null> , StarWars Bill , XMEN , <null> Bill , Terminator 2 , <null> Bill , <null> , Terminator What I am getting is something like. User, DVD , VHS Fred, XMEN , StarWars Fred, MontyPhthon Holy Grail , StarWars Bill , XMEN , Terminator Bill , Terminator 2 , Terminator Any help would be apreciated. Thanks,
__________________
Mark Pearce
|
|
#2
February 18th, 2004, 07:55 AM
|
||||
|
||||
|
Code:
select UserName
, DVDName as DVD
, null as VHS
from Users
inner
join DVDUsers
on Users.ID = DVDUsers.UserID
inner
join DVD
on DVDUsers.DVD_ID = DVD.ID
union all
select UserName
, null
, VHSName
from Users
inner
join VHSUsers
on Users.ID = VHSUsers.UserID
inner
join VHS
on VHSUsers.VHS_ID = VHS.ID
|
|
#3
February 18th, 2004, 09:52 AM
|
||||
|
||||
|
Hi,
That is great.. The only problem I have now, is that I need it to be grouped by name. I know this may be an issue using the UNION command, and understand if it's just not possible. Using the amazing query that you gave me, I get the following. PHP Code:
I would like it to look like this. PHP Code:
Thanks for your help. Mark Pearce
|
|
#4
February 18th, 2004, 09:57 AM
|
||||
|
||||
|
Forget it... I just stuck "order by Name ,DVD ,VHS" on the end and it works fine. I thought I had read somewhere that once you do a UNION, you can't do any other sorting or conditional statements.
Again, thank you very very very much. You are the Master..  Guess that's why you have almost 2000 posts.
|
|
#5
February 18th, 2004, 10:39 AM
|
||||
|
||||
|
More complexity...
Sorry to bother you again, but I was just wondering if it is possible to add a bit more complexity to this situation.
Lets say you wanted to get this report to include at a higher level, the store and state of the store. So, you would then have.. PHP Code:
So, I added a State table, a Store table and a StateID and StoreID fields to the users table. State table: ID, State Store Table: ID, Store Users Table: ID, Name, StateID, StoreID Would this be possible? I do have a reason for asking, I'm not just trying to be a pain in the butt.. I actually already have this outputting the way I want, but I did it using nested loops and nested DB connections and that is not a good way to do this. But, trying to turn it into a flat table is not being much fun either. Last edited by mpearce : February 18th, 2004 at 10:54 AM.
|
|
#6
February 18th, 2004, 11:22 AM
|
|||
|
|||
|
If I may...
First, the "State Code" (MA, CA, etc) is already a Primary Key, so you don't need a table just to generate a Key to represent that. You are only making more work for yourself. You only need a "State" table if you want to make sure (via foriegn key) that only existing States are entered in any other table, and/or if you need to store more information about the State (Full state name, state flower, state capital...). Instead store State as an attribute of Store. Store Table: ID, Store, StateCode Then, from Rudy's query: Code:
select StateCode
, Store
, UserName
, DVDName as DVD
, null as VHS
from Users
inner
join Store
on Store.ID = Users.StoreID
inner
join DVDUsers
on Users.ID = DVDUsers.UserID
inner
join DVD
on DVDUsers.DVD_ID = DVD.ID
union all
select StateCode
, Store
, UserName
, null
, VHSName
from Users
inner
join Store
on Store.ID = Users.StoreID
inner
join VHSUsers
on Users.ID = VHSUsers.UserID
inner
join VHS
on VHSUsers.VHS_ID = VHS.ID
|
|
#7
February 18th, 2004, 02:12 PM
|
||||
|
||||
|
OK, I understand what you are saying about the state table. The video stuff is just an example of what am doing, so my actual data doesn't have a State table. I started with the VHS/DVD thing just to have something quick to throw up here. Thankfully, you were very helpful, so I decided to ask about the next step. I had to make up some tables to go with the DVD/VHS thing and I just picked State and Store out of the air.
Anyway, I think I am all set now, your examples were great and were able to show me what I had to do. I used the MS SQL Query builder thingy to help me with the sub queries and then just stuck them together with a Union All as you showed me. Can you just take one more quick look at my final query and tell me if it looks ok. Should both halfs of the union look the same? Thank you very much for your help. Code:
SELECT State
, Store
, Name
, NULL AS DVD
, VHSName AS VHS
FROM vhs
INNER
JOIN vhsusers
ON vhs.ID = vhsusers.VHS_ID
INNER
JOIN users
INNER
JOIN State
ON users.StateID = State.ID
INNER
JOIN Store
ON users.StoreID = Store.ID
ON vhsusers.UserID = users.ID
union all
SELECT State
, Store
, users.Name
, dvd.DVDName AS DVD
, NULL AS VHS
FROM users
INNER
JOIN State
ON users.StateID = State.ID
INNER
JOIN dvdusers
ON users.ID = dvdusers.UserID
INNER
JOIN dvd
ON dvdusers.DVD_ID = dvd.ID
order by State, Store, name, dvd, vhs
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Help with query. Joins? Unions? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
