Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
geossl
Yak Posting Veteran
85 Posts |
 Posted - 2009-10-06 : 22:48:31
|
| Dear All, There are 4 tables Society, Salon, Entry, Photo. Society will organize Salon , and a participant may submit a number of entries for each salon. A participant has a collection of photos.Society (Table)Id (int - Primary Key)Name (nvarchar)Country (nvarchar)SalonId (int - Primary Key)SocietyID (int - Ref Key)SalonDate (DateTime)EntryId (int - Primary Key)SalonID (int - Ref Key) PhotoID (int - Ref Key)Accepted (bit)PhotoId (int - Primary key)Title (nvarchar)CTitle (nvarchar)I would like to use a SQL query to get a list of Society and photos that have not been accepted or have not been submitted for salon.I can only think of way to use programmatical to generate the result.Any idea to use SQL? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-10-06 : 22:57:13
|
how do you associate a Society to Photo if a photo has not been submitted ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
geossl
Yak Posting Veteran
85 Posts |
Posted - 2009-10-06 : 23:06:10
|
Society (one to many) -> SalonSalon (one to many) -> EntryPhoto (one to Many) -> Entryquote:
Originally posted by khtan
how do you associate a Society to Photo if a photo has not been submitted ?
KH[spoiler]Time is always against us[/spoiler]
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-10-06 : 23:11:34
|
quote:
Originally posted by geossl
Society (one to many) -> SalonSalon (one to many) -> EntryPhoto (one to Many) -> Entryquote:
Originally posted by khtan
how do you associate a Society to Photo if a photo has not been submitted ?
KH[spoiler]Time is always against us[/spoiler]
Yes. But your question isquote:
I would like to use a SQL query to get a list of Society and photos that have not been accepted or have not been submitted for salon.
So for a Photo that has not been submitted, there will not be any Society associate with it right ? A photo will be associated to the Society after it is submitted and has a record in Entry table.So how do you want the result of the query looks like for such case ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-10-06 : 23:28:51
|
if you are not sure, post some sample data and show us your expected result.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
geossl
Yak Posting Veteran
85 Posts |
Posted - 2009-10-07 : 02:20:36
|
I think you have been misunderstood.The result is to get a list of photos that can be submitted to the Salon. Photos that have been accepted cannot be submitted again.quote:
Originally posted by khtan
quote:
Originally posted by geossl
Society (one to many) -> SalonSalon (one to many) -> EntryPhoto (one to Many) -> Entryquote:
Originally posted by khtan
how do you associate a Society to Photo if a photo has not been submitted ?
KH[spoiler]Time is always against us[/spoiler]
Yes. But your question isquote:
I would like to use a SQL query to get a list of Society and photos that have not been accepted or have not been submitted for salon.
So for a Photo that has not been submitted, there will not be any Society associate with it right ? A photo will be associated to the Society after it is submitted and has a record in Entry table.So how do you want the result of the query looks like for such case ?
KH[spoiler]Time is always against us[/spoiler]
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-10-07 : 06:38:01
|
| SELECT * FROM Photo PWHERE NOT EXISTS(SELECT * FROM Entry WHERE PhotoID=P.ID AND Accepted=1) |
|
|
|
|
|
|
|
|
Difficult SQL