| Author |
 Topic  |
|
|
rjrferreira
Starting Member
13 Posts |
 Posted - 03/15/2007 : 10:52:10
|
I all,
i have a problem with a query.
I have two tables (Software and Licenses), and one software can have one or more licenses.
So what i want to get from this query is:
- all info of the software table
- and, if the software have or not, attributed licenses.
How can i do that? |
|
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 03/15/2007 : 10:54:25
|
Use an outer join
SELECT *
FROM Software
LEFT OUTER JOIN Licenses ON Software.PKID = Licenses.FKID |
 |
|
|
rjrferreira
Starting Member
13 Posts |
Posted - 03/15/2007 : 10:59:09
|
| sorry snSQL but doesn't work that query! |
|
|
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 03/15/2007 : 11:01:45
|
| So you just tried to run that query and it didn't work - huh! You did not give the names of the columns in your tables, so of course it won't work, you'll need to replace the names in the query I gave you with your names. That was just an example! |
|
|
|
rjrferreira
Starting Member
13 Posts |
Posted - 03/15/2007 : 11:10:09
|
quote:
Originally posted by snSQL
So you just tried to run that query and it didn't work - huh! You did not give the names of the columns in your tables, so of course it won't work, you'll need to replace the names in the query I gave you with your names. That was just an example!
I know that!!! :D |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/15/2007 : 11:15:46
|
then post your table DDL, some sample data and the expected result.
KH
|
|
|
|
rjrferreira
Starting Member
13 Posts |
Posted - 03/15/2007 : 11:28:06
|
My table Software have this info:
- id
- softname
- version
- state
My table License have this info:
- id
- softID (FK)
- serial
- username
In table License in field username i put the name of the user that have the licence.
So what i want with the query is something like this:
ID|softname|version|state|allLicencesFree
-----------------------------------------
1|microsoft|200|A|1
2|oracle|9.0|A|0
In field allLicencesFree "1" is if all Licences are Free, and "0" if not |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 03/15/2007 : 11:33:41
|
SELECT s.ID, s.SoftName, s.Version, s.State, l.ID, l.Serial, l.UserName
FROM Software AS s
LEFT JOIN Licenses AS l ON l.SoftID = s.ID
Peter Larsson
Helsingborg, Sweden |
|
|
|
rjrferreira
Starting Member
13 Posts |
Posted - 03/15/2007 : 11:39:55
|
quote:
Originally posted by Peso
SELECT s.ID, s.SoftName, s.Version, s.State, l.ID, l.Serial, l.UserName
FROM Software AS s
LEFT JOIN Licenses AS l ON l.SoftID = s.ID
Peter Larsson
Helsingborg, Sweden
|
|
|
|
rjrferreira
Starting Member
13 Posts |
Posted - 03/15/2007 : 11:41:56
|
quote:
Originally posted by Peso
SELECT s.ID, s.SoftName, s.Version, s.State, l.ID, l.Serial, l.UserName
FROM Software AS s
LEFT JOIN Licenses AS l ON l.SoftID = s.ID
Peter Larsson
Helsingborg, Sweden
With that query all licenses appears, but i don't want that!!!
I want that one colunm of the result say to me:
"This software don't have any attributed license, or have."
and i just want one line per software. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 03/15/2007 : 11:50:18
|
SELECT s.SoftName, s.Version, case when l.softid is null then 'No license' else 'License' end
FROM Software AS s
LEFT JOIN Licenses AS l ON l.SoftID = s.ID
Peter Larsson
Helsingborg, Sweden |
|
|
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 03/15/2007 : 13:09:21
|
quote:
With that query all licenses appears, but i don't want that!!!
I want that one colunm of the result say to me:
"This software don't have any attributed license, or have."
That's not what you said, you said you wanted the allLicensesFree column, but you didn't tell us how you know if a license is free. So here is what you want without the allLicensesFree column
SELECT Q.[ID], Q.softname, Q.version, Q.state,
CASE WHEN Q.Licensed = 0
THEN 'This software don''t have any attributed license'
ELSE 'This software do have any attributed license' as licensed
(SELECT S.[ID], S.softname, S.version, S.state, MAX(ISNULL(L.SoftID, 0)) AS Licensed
FROM Software S
LEFT OUTER JOIN Licenses L ON S.[ID] = L.SoftID
GROUP BY S.[ID], S.softname, S.version, S.state) AS Q |
Edited by - snSQL on 03/15/2007 13:16:10 |
|
|
| |
Topic |
|
Query 2 tables
