?
Solved

return only joined results is the results are not joined more than x times

Posted on 2011-10-27
7
Medium Priority
?
319 Views
Last Modified: 2012-05-12
Hi there experts,

I have two tables: table 1 with names and table 2 with dates and times
columns in table 1:
id, firstname, lastname, appointment

columns in table 2:
id, date, time, number

column 'appointment' contains the id's of colums 2. I can now plan an appointment for a certain person.
The number of persons on a single appointments is limited. Therefore I added the column 'number' which contains an integer value representing the maximum number of persons.
In my form I would now like to filter the appointments that are not fully booked yet.

So:
I want to show the table 2 id's where the count of table 1 appointment values is less than the maximum number of persons for that specific date/time

Anybody?
0
Comment
Question by:arf73
  • 3
  • 2
  • 2
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37043058
table2 is the appointment table?
table1 is the list of people for that appointment?

so, it would be:
select t2.*
  from table2 t2
 where t2.number > ( select count(*) from table1 t1 where t1.appointment = t2.id )

Open in new window

0
 
LVL 5

Expert Comment

by:mcs0506
ID: 37043126
Hi
Here is the Sample query for you problem

SELECT  FirstName ,
        LastName ,
        appTable.AppCount AppointmentCount ,
        T1.Appointment AppointmentId ,
        appTable.[Date] ,
        appTable.[Time] ,
        appTable.Number
FROM    T1
        INNER JOIN ( SELECT T2.Id ,
                            COUNT(T1.Appointment) AppCount ,
                            Number ,
                            T2.[Date] ,
                            T2.[Time]
                     FROM   T1
                            INNER JOIN T2 ON T1.Appointment = T2.Id
                     GROUP BY T1.Appointment ,
                            T2.Id ,
                            T2.Number ,
                            T2.[Date] ,
                            T2.[Time]
                   ) appTable ON dbo.T1.Appointment = appTable.Id
WHERE   appTable.AppCount < appTable.Number





(T1 = Table and T2=Table2)

Regards

Dani
0
 

Author Comment

by:arf73
ID: 37043902
@ angelIII what does the t2 stand for in table 2 t2?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 37044024
it's the format:
select * from table_name alias_name

you can give an (usually shorter) alias name to the table for the query, so you can prefix the column names with the alias instead of the full table name to keep the query readable.

select t1.column_from_table_1, t2.column_from_table_2 from my_long_table_name_1 t1 join my_long_table_name_2 t2 on t2.pk = t1.pk
vs:
select my_long_table_name_1.column_from_table_1, my_long_table_name_2.column_from_table_2 from my_long_table_name_1 join my_long_table_name_2 on my_long_table_name_2.pk = my_long_table_name_1.pk
0
 

Author Comment

by:arf73
ID: 37044558
@ angellll Right, I thought the syntax was select * from table_name AS alias name. I will give it a try, thanks.
0
 
LVL 5

Expert Comment

by:mcs0506
ID: 37044609
Hi
If you want to get all data from both Table1 and Table2 then you can use the following

SELECT  FirstName ,
        LastName ,
        appTable.AppCount AppointmentCount ,
        Table1.Appointment AppointmentId ,
        appTable.[Date] ,
        appTable.[Time] ,
        appTable.Number
FROM    Table1
        INNER JOIN ( SELECT Table2.Id ,
                            COUNT(Table1.Appointment) AppCount ,
                            Number ,
                            Table2.[Date] ,
                            Table2.[Time]
                     FROM   Table1
                            INNER JOIN Table2 ON Table1.Appointment = Table2.Id
                     GROUP BY Table1.Appointment ,
                            Table2.Id ,
                            Table2.Number ,
                            Table2.[Date] ,
                            Table2.[Time]
                   ) appTable ON dbo.Table1.Appointment = appTable.Id
WHERE   appTable.AppCount < appTable.Number


Dani
0
 

Author Closing Comment

by:arf73
ID: 37044634
Thanks a lot
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month13 days, 22 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question