CODEWITHSUNDEEP
sqlsql-servermany-to-manyone-to-many
chilleo
chilleo

Reputation: 546

how do i get the results of a many to many style query to act like a one to many query in SQL?

OK I will try to outline a simple example to demonstrate what I would like to do.

Lets say we had a table about people who owned pets and they could only have one cat and one dog. sorry im not sure how to make tables very well on stack overflow posts it doesn't seem to want to let me use html table tags. eg:

Table name: PetOwners

columns: OwnerName, Cat, Dog

example rows:

Bob,Saimese,Lab
Cindy,Tabby,Poodle

But then I wanted to implement this in a many to many fashion for other reasons such as for planning for the future of the DB. So I split this into tables:

Table name: Owners

columns: OwnerID,OwnerName

example rows:

1,Bob
2,Cindy

Table name: Pets

columns: OwnerID,Pet,isCat,isDog

example rows:

1,Saimese,y,n
2,Lab,n,y
3,Tabby,y,n
4,Poodle,n,y


Table name: Link

columns: OwnerID,PetID

example rows:

1,1
1,2
2,3
2,4

So now my question is how do I spoof the results of a select all from my original table (select * from PetOwners) with my current structure assuming all the data will be like this and each pet owner will own one cat and one dog.

Note: My question is specifically using SQL, I can do this in the programming language I am calling the SQL from if I need to, but I am wanting to know if I can just do it right there in the SQL. If it cannot be done that is OK you can say that, I am just wondering

EDIT

ok my apologies but i dont think i was clear, i would like to try and return the results back as if i had run select * from PetOwners. aka i want to be able to see it in a single line for instance:

Bob,Saimese,Lab

when i try running the query from answer #1 i get:

Bob,NULL,Lab Bob,Saimese,NULL

this is what i have been struggling with and therefore the reason for me posting this question. again im not sure u can write SQL to get it to actually return on one line but let me know if there is, thanks. oh and i actually mispoke about the table structure in the first question i am using three tables to emulate many to many i will edit it to show this but i dont think it really changes anything (you just join the "linkage table" with pet owners first of course). thanks!

Upvotes: 0

Views: 212

Answers (2)

GarethD
GarethD

Reputation: 69769

SELECT   OwnerName, cat.Pet, dog.Pet
FROM     Owners
         INNER JOIN Pets cat
             ON cat.OwnerID = Owners.OwnerID
             AND cat.IsCat = 'Y'
             AND cat.IsDog = 'N'
         INNER JOIN Pets dog
             ON dog.OwnerID = Owners.OwnerID
             AND dog.IsCat = 'N'
             AND dog.IsDog = 'Y'

Although I would take on board David Beber's suggestion of a PetType column rather than multiple Y/N flags.

Upvotes: 2

David Faber
David Faber

Reputation: 12485

SELECT ownerID, cat AS pet, 'y' AS isCat, 'n' AS isDog
  FROM PetOwners
 WHERE cat IS NOT NULL
 UNION
SELECT ownerID, dog AS pet, 'n' AS isCat, 'y' AS isDog
  FROM PetOwners
 WHERE dog IS NOT NULL

That should do the trick. As an aside, you might be better off using a petType column rather than using a bunch of flags - the way you have it set up, you'll have to add a column when someone owns a rabbit, ferret, etc.

Upvotes: 2

Related Questions